using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Collections;
using System.Text;
using Norm.Attribute;
using Norm.Generic;

namespace Norm.Helper
{
    /// <summary>
    /// Convert Sql to Objects
    /// Non tested version of SqlHelper but accepts Propertys and Fields 
    /// </summary>
    public sealed class SqlHelper2
    {
        /// <summary>
        /// Reads the current row from the reader object and 
        /// assigns fields with columns matching their names
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="o"></param>
        public static object Convert(SqlDataReader reader, object o)
        {
            if (!reader.HasRows)
                throw new Exception.UnableToRead();

            MemberInfo[] fields = DatabaseFields(o);//o.GetType().GetFields();
            string tableName = TableName(o);
            foreach (MemberInfo mi in fields)
            {
                if (DatabaseField(mi).IsComplexObject)
                {
                    ObjHelper.SetMember(o,
                        Convert(reader, 
                            ObjHelper.GetMemberValue(o, mi)), 
                        mi);
                    //fi.SetValue(o, Convert(reader, fi.GetValue(o)));
                    continue;
                }

                object val;
                try
                {
                    val = reader[string.Format(
                            "{0}_{1}",
                                tableName,
                                mi.Name)];
                    ObjHelper.SetMember(o,val,mi);
                }
                catch { continue; }
            }
            return o;
        }

        public static MemberInfo[] DatabaseFields(object o)
        { return DatabaseFields(o.GetType()); }

        public static MemberInfo[] DatabaseFields(Type t)
        {
            List<MemberInfo> list = new List<MemberInfo>();
            foreach (MemberInfo fi in t.GetMembers())
                if (ObjHelper.MemberAttributes(fi, typeof(Attribute.DatabaseField)).Length != 0)
                    list.Add(fi);
            return list.ObjArray();
        }

        public static Attribute.DatabaseField DatabaseField(MemberInfo field)
        {
            Attribute.DatabaseField[] atts = (Attribute.DatabaseField[])Helper.ObjHelper.MemberAttributes(field, typeof(Attribute.DatabaseField));
            if (atts != null && atts.Length > 0)
                return atts[0];
            return null;
        }

        public static string TableName(Type t)
        {
            return TableName(ObjHelper.NewObj(t));
        }

        public static string TableName(object o)
        {
            string name = o.GetType().Name;
            foreach (object att in ObjHelper.ClassAttributes(o, typeof(Attribute.Table)))
                if (att != null &&
                    !string.IsNullOrEmpty(((Attribute.Table)att).TableName))
                    name = ((Attribute.Table)att).TableName;
            return name;
        }

        public static void ValidateReader(SqlDataReader reader)
        {
            if (reader == null) throw new Exception.UnableToRead();
            if (!reader.HasRows) throw new Exception.UnableToRead();
        }

        public static MemberInfo PrimaryKeyField(Type t)
        {
            return PrimaryKeyField(Helper.ObjHelper.NewObj(t));
        }
        public static MemberInfo PrimaryKeyField(object o)
        {
            MemberInfo[] mis = DatabaseFields(o);
            foreach (MemberInfo mi in mis)
            {
                DatabaseField dbf = DatabaseField(mi);
                if (dbf.IsPrimaryKey)
                    return mi;
            }
            throw new Exception.NoKeys();
        }

        /// <summary>
        /// FIXME: IsEnum Logic partially implemented 
        /// </summary>
        /// <param name="o"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public static object CreateObject(object o, SqlConnection c)
        {
            //Create all complex objects below us
            foreach (MemberInfo mi in ComplexFields(o))
            {
                //If fobj is EnumTable dont recurse
                bool isEnum = false;
                if (DatabaseField(mi).IsComplexObject)
                {
                    Table[] atts = (Table[])ObjHelper.ClassAttributes(ObjHelper.GetMemberValue(o,mi), typeof(Attribute.Table));
                    if (atts != null && atts.Length > 0)
                        foreach (Table att in atts)
                            if (att.IsEnum)
                            { isEnum = true; break; }
                }

                //other logic might be needed here later to handle other
                //"unique" flags on objs

                //if we arent an enum create the object
                if (!isEnum)
                {
                    object oc = CreateObject(ObjHelper.GetMemberValue(o,mi), c);
                    ObjHelper.SetMember(o, oc, mi);
                }
            }

            object val = Create(o, c).ExecuteScalar();

            if(ObjHelper.IsWritableMember(PrimaryKeyField(o)))
                ObjHelper.SetMember(o,val,PrimaryKeyField(o));

            return o;
        }

        public static MemberInfo[] UniqueColumns(object o)
        {
            Norm.Generic.List<MemberInfo> mis = new Norm.Generic.List<MemberInfo>();
            foreach (MemberInfo mi in DatabaseFields(o))
                if (DatabaseField(mi).IsUniqueColumn)
                    mis.Add(mi);
            return mis.ObjArray();
        }

        /// <summary>
        /// Creates the query needed to insert the object into the db (does not support complex yet)
        /// </summary>
        /// <param name="o"></param>
        /// <param name="cmd">ref to the SqlCommand where params will be added as the cmd is built</param>
        /// <returns></returns>
        public static string InsertObjectQuery(object o, ref SqlCommand cmd)
        {
            //Make sure the guid is set
            FieldInfo boidField = o.GetType().GetField("BaseObjectId");
            if (boidField != null && Guid.Equals(boidField.GetValue(o), Guid.Empty))
                boidField.SetValue(o, Guid.NewGuid());

            string tableName = TableName(o);
            StringBuilder sb = new StringBuilder(string.Format
                ("declare @{0} int;",
                    PrimaryKeyField(o).Name));

            MemberInfo[] uniqueCols = UniqueColumns(o);
            if (uniqueCols.Length > 0)
            {
                //Declare a var to hold out counts
                sb.AppendFormat("declare @{0}_count int;", tableName);

                //Select the total of time our unique criteria occurs
                sb.AppendFormat("select @{0}_count = count(*) from [{0}] where ", tableName);
                foreach (MemberInfo mi in uniqueCols)
                {
                    string varName = string.Format("@unique_{0}_{1}", tableName, mi.Name);
                    sb.AppendFormat("[{0}] = {1} and", mi.Name, varName);
                    cmd.Parameters.AddWithValue(varName, ObjHelper.GetMemberValue(o,mi));
                }
                sb = new StringBuilder(sb.ToString().TrimEnd(" and".ToCharArray())).Append(";");

                //If the count is > 0 
                sb.AppendFormat("if @{0}_count > 0 begin\n", tableName);

                //Then there is no reason to insert the record
                //Just select it
                sb.Append(string.Format
                    ("select top 1 @{0} = [{0}] from [{1}] where ", PrimaryKeyField(o).Name, tableName));
                foreach (MemberInfo mi in uniqueCols)
                {
                    string varName = string.Format("@unique_{0}_{1}", tableName, mi.Name);
                    sb.AppendFormat("[{0}] = {1} and", mi.Name, varName);
                }
                sb = new StringBuilder(sb.ToString().TrimEnd(" and".ToCharArray())).Append(";");
                sb.AppendFormat("select cast(@{0} as int);", PrimaryKeyField(o).Name);

                //else we need to insert
                sb.Append("end else begin ");
            }

            sb.Append(string.Format
                ("insert into [{0}] (",
                    tableName));

            //Append column list for all non pkeys
            foreach (MemberInfo mi in DatabaseFields(o))
                if (DatabaseField(mi).Stored)
                    sb.Append(string.Format
                        ("[{0}],", mi.Name));
                else if (DatabaseField(mi).IsComplexObject)
                    sb.Append(string.Format
                        ("[{0}],", ForeignKey(o, mi).Name));

            //Remove extra trailing comma
            sb = new StringBuilder(sb.ToString().TrimEnd(",".ToCharArray()));

            //Begin inserting of values
            sb.Append(" ) values (");

            //Append each var in order
            foreach (MemberInfo mi in DatabaseFields(o))
            {
                DatabaseField dbf = DatabaseField(mi);

                //Dont insert pkey, this will fail as identity should be enabled
                if (dbf.IsPrimaryKey)
                    continue;

                //Figure out what param to call this
                //If it is a complex object we will use the @ForiegnKey
                //otherwise the variable will be called TableName_ColumnName
                //Note: ForeignKey must be defined in this query
                string paramName;
                if (dbf.IsComplexObject)
                    paramName = string.Format
                        ("@{0}", ForeignKey(o, mi).Name);
                else
                    paramName = string.Format
                        ("@{0}_{1}", tableName, mi.Name);

                sb.Append(string.Format("{0},", paramName));

                //If not a complex object add the var otherwise it should have been added when the object is created
                if (!dbf.IsComplexObject)
                    cmd.Parameters.AddWithValue(paramName, ObjHelper.GetMemberValue(o,mi));
            }
            sb = new StringBuilder(sb.ToString().TrimEnd(",".ToCharArray())).Append(");");

            //set the pkey in the query
            //If there are unique columns then we dont need to do the selecting of the identity
            //  it would be an extra step and would contain random data
            if (uniqueCols.Length == 0)
                sb.Append(string.Format
                    ("set @{0} = cast(@@IDENTITY as int);", PrimaryKeyField(o).Name));
            else
                sb.Append(" end;");

            return sb.ToString();
        }

        public static SqlCommand Create(object o)
        {
            if (o == null)
                throw new Exception.BlankObject();

            string tableName = TableName(o);
            SqlCommand cmd = new SqlCommand();
            StringBuilder sb = new StringBuilder();

            foreach (MemberInfo mi in ComplexFields(o))
            {
                //Dont recurse into enum tables
                bool shouldCreate = true;
                object fo = ObjHelper.GetMemberValue(o,mi);
                foreach (Attribute.Table att in ObjHelper.ClassAttributes(fo, typeof(Attribute.Table)))
                    if (att.IsEnum)
                    {
                        cmd.Parameters.AddWithValue(string.Format(
                            "@{0}",
                            PrimaryKeyField(fo).Name),
                            ObjHelper.GetMemberValue(fo,PrimaryKeyField(fo)));
                        shouldCreate = false;
                        break;
                    }

                if (shouldCreate)
                    sb.Append(InsertObjectQuery(ObjHelper.GetMemberValue(o,mi), ref cmd));
            }
            sb.Append(InsertObjectQuery(o, ref cmd));

            cmd.CommandText = sb.Append(";select cast(@@IDENTITY as int)").ToString();
            return cmd;
        }

        public static bool IsStoredField(string field, object o)
        {
            foreach (MemberInfo mi in o.GetType().GetMember(field))
            {
                object[] atts = ObjHelper.MemberAttributes(mi, typeof(Attribute.DatabaseField));

                if (atts == null || atts.Length == 0)
                    continue;

                if (((Attribute.DatabaseField)atts[0]).Stored)
                    return true;
            }
            return false;
        }

        public static SqlCommand Create(object o, SqlConnection c)
        {
            SqlCommand cmd = Create(o);
            cmd.Connection = c;
            return cmd;
        }

        /// <summary>
        /// Convert the C# type to DB Type
        /// </summary>
        /// <param name="fi"></param>
        /// <returns></returns>
        public static string DatabaseType(MemberInfo mi)
        {
            //Get the type name
            string type = ObjHelper.TypeOfMember(mi).Name;
            switch (type)
            {
                case "System.String": return "varchar(MAX)";
                case "System.Int32": return "int";
                case "System.Double": return "double";
                case "System.DateTime": return "datetime";
                case "System.Guid": return "uniqueidentifier";
                case "System.Boolean": return "bit";
                default: throw new Exception.NotValidEnumValue();
            }
        }

        /// <summary>
        /// Get the query that would be needed to create the table to store the obj
        /// </summary>
        /// <param name="o"></param>
        /// <returns>query text</returns>
        public static string CreateTableQuery(object o)
        {
            //Make sure there are fields to this table
            //Otherwise just throw NoKeys exception
            MemberInfo[] fields = DatabaseFields(o);
            if (fields.Length == 0)
                throw new Exception.NoKeys();

            //Get the table name and begin constructing the query
            string tableName = SqlHelper.TableName(o);
            StringBuilder sb = new StringBuilder(
                string.Format("create table [{0}] ( ", tableName));

            foreach (MemberInfo mi in fields)
            {
                DatabaseField dbf = DatabaseField(mi);

                //If the field isnt stored and is not a primary key skip                
                if (!dbf.Stored && !dbf.IsPrimaryKey && !dbf.IsComplexObject)
                    continue;

                //Declare var
                string colType;
                string colName;

                //If this is not a complex object do normal actions
                if (!dbf.IsComplexObject)
                {
                    colType = DatabaseType(mi);
                    colName = mi.Name;
                }
                else //create column as a foreign key
                {
                    if (ObjHelper.TypeOfMember(mi).IsArray)
                        continue;

                    MemberInfo fkey = ForeignKey(o, mi);
                    colName = fkey.Name;
                    colType = DatabaseType(fkey);
                }

                sb.Append(string.Format("[{0}] {1} ", colName, colType));

                //Mark primary key if needed
                if (dbf.IsPrimaryKey)
                    if (colType.Equals("uniqueidentifier"))
                        sb.Append("primary key RowGuidcol ");
                    else
                        sb.Append("primary key identity(1,1) ");

                //Append ,
                sb.Append(", ");
            }

            //Remove the trailing ', ' from the last field
            sb = new StringBuilder(sb.ToString().TrimEnd(", ".ToCharArray())).Append(");");

            //Add unique indexes to the table
            foreach (MemberInfo mi in UniqueColumns(o))
                sb.AppendFormat("create unique index UQ_{0}_{1} on [{0}]([{1}]);", tableName, mi.Name);


            //Append array tables if needed
            sb.Append(CreateArrayTables(o));

            return sb.ToString();
        }

        //Create a table and return the success code
        public static int CreateTable(object o, SqlConnection c)
        {
            using (SqlCommand cm = new SqlCommand(CreateTableQuery(o), c))
            {
                cm.CommandType = CommandType.Text;
                return cm.ExecuteNonQuery();
            }
        }

        public static string ArrayTableName(object o, MemberInfo mi)
        {
            return string.Format
                ("mt_{0}_{1}_{2}",
                new object[] { 
                    TableName(o), 
                    TableName(ObjHelper.InstanceOfArrayType(mi)), 
                    mi.Name });
        }

        /// <summary>
        /// Creates the query to add middle tables to store arrays
        /// Note: The blog outputs a check if the object exists if so nothing will happen (no drops)
        /// </summary>
        /// <param name="o"></param>
        /// <returns>query text</returns>
        public static string CreateArrayTables(object o)
        {
            StringBuilder sb = new StringBuilder();
            foreach (FieldInfo fi in DatabaseFields(o))
            {
                if (!fi.FieldType.IsArray)
                    continue;

                object fo = fi.GetValue(o);
                if (fo == null)
                {
                    //HACK: Turn 'fish.obj[]' into 'fish.obj'
                    string typeName = fi.FieldType.FullName.TrimEnd("[]".ToCharArray());
                    fo = fi.FieldType.Assembly.CreateInstance(typeName);
                }

                string ftable = TableName(fo);
                string middleTable = ArrayTableName(o, fi);

                sb.AppendFormat("if object_id(N'{0}' is null begin ", middleTable);
                sb.AppendFormat
                    (@"create table [{0}] 
                        ( [{0}Id] int primary key identity(1,1),
                            [{1}_BaseObjectId] uniqueidentifier not null,
                            [{2}_BaseObjectId] uniqueidentifier not null);",
                    new object[] { middleTable, TableName(o), ftable });
                sb.Append("end;");
            }
            return sb.ToString();
        }

        public static string SelectCols(object o)
        {
            StringBuilder sb = new StringBuilder();
            string tableName = TableName(o);

            foreach (MemberInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).IsComplexObject)
                    sb.Append(SelectCols(ObjHelper.GetMemberValue(o,fi))).Append(",");
                else
                    sb.Append(string.Format
                        ("[{0}].[{1}] as [{0}_{1}],", tableName, fi.Name));

            return sb.ToString().Trim(",".ToCharArray());
        }

        public static object RetrieveObject(object o, object pk, SqlConnection c)
        {
            string query = string.Format(
                @"select top 1 {0} from [{1}] where [{2}] = @pkey",
                    new object[]{
                    SelectCols(o),
                    SqlHelper.TableName(o),
                    PrimaryKeyField(o).Name});

            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@pkey", pk);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                        return SqlHelper.Convert(reader, o);
                }

                throw new Exception.BlankObject();
            }
        }

        /// FIXME: Needs to support Complex objects
        public static int UpdateObject(object o, SqlConnection c)
        {
            //Blank or null pk dont continue as this could be REALLY bad for our system
            if (string.IsNullOrEmpty
                (System.Convert.ToString
                    (ObjHelper.GetMemberValue(o,PrimaryKeyField(o)))))
                        throw new Exception.BlankObject();

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = c;
                cmd.CommandType = CommandType.Text;

                //build the query
                string query = string.Format("update [{0}] set ", TableName(o));

                //add params to update
                foreach (MemberInfo mi in DatabaseFields(o))
                {
                    //Dont update the pk, the update will always fail
                    DatabaseField dbf = DatabaseField(mi);
                    if (dbf.IsPrimaryKey)
                        continue;

                    //Complex objects just recurse into
                    if (dbf.IsComplexObject)
                    {
                        object fo = ObjHelper.GetMemberValue(o, mi);
                        MemberInfo fkey = ForeignKey(o, mi);

                        //small optimization
                        //if the for obj is an Enum table dont update it
                        //to update enums pass the enum in explicitly
                        object[] atts = ObjHelper.ClassAttributes(fo, typeof(Attribute.Table));
                        bool Recurse = true;
                        if (atts != null)
                            foreach (object att in atts)
                                if (((Attribute.Table)att).IsEnum)
                                    Recurse = false;
                        if (Recurse)
                            UpdateObject(fo, c);

                        query += string.Format
                            ("[{0}]=@{0}, ", fkey.Name);

                        cmd.Parameters.AddWithValue
                            ("@" + fkey.Name, ObjHelper.GetMemberValue(fo,fkey));
                    }
                    else
                    {
                        query += string.Format("[{0}]=@{0}, ", mi.Name);
                        cmd.Parameters.AddWithValue(mi.Name, ObjHelper.GetMemberValue(o, mi));
                    }
                }

                //remove trailing comma
                query = query.TrimEnd(", ".ToCharArray());

                //set the where clause
                query += string.Format(" where [{0}] = @pkey", PrimaryKeyField(o).Name);
                cmd.Parameters.AddWithValue("@pkey", ObjHelper.GetMemberValue(o,PrimaryKeyField(o)));

                //set the cmd to use query
                cmd.CommandText = query;

                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// FIXME: Does not support complex objects
        /// </summary>
        /// <param name="o"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public static int DeleteObject(object o, SqlConnection c)
        {
            string query = string.Format("delete from [{0}] where [{1}] = @pkey",
                TableName(o), PrimaryKeyField(o).Name);

            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                cmd.Parameters.AddWithValue("@pkey", ObjHelper.GetMemberValue(o,PrimaryKeyField(o)));
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Get the fields/properties which have been marked as complex
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static MemberInfo[] ComplexFields(object o)
        {
            Generic.List<MemberInfo> fis = new Norm.Generic.List<MemberInfo>();
            foreach (MemberInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).IsComplexObject)
                    fis.Add(fi);
            return fis.ObjArray();
        }

        public static MemberInfo[] SearchableTextFields(Type t)
        { return SearchableTextFields(Helper.ObjHelper.NewObj(t)); }
        public static MemberInfo[] SearchableTextFields(object o)
        {
            Generic.List<MemberInfo> fs = new Norm.Generic.List<MemberInfo>();
            foreach (MemberInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).IsSearchableText)
                    fs.Add(fi);
            return fs.ObjArray();
        }

        /// <summary>
        /// Get the foreign object primary key (as MemberInfo) for the specified member of object o
        /// </summary>
        /// <param name="o">Parent Object</param>
        /// <param name="fi"></param>
        /// <returns></returns>
        public static MemberInfo ForeignKey(object o, MemberInfo fi)
        {
            return PrimaryKeyField(ObjHelper.GetMemberValue(o,fi));
        }

        public static string RetrieveComplexQuery(object o)
        {
            if (o == null)
                throw new Norm.Exception.BlankObject();
            //Construct query
            string query = string.Format("select {0} from [{1}] ", SelectCols(o), TableName(o));

            //Assemble inner joins
            string pTable = TableName(o);
            foreach (MemberInfo mi in ComplexFields(o))
            {
                string fTable = TableName(ObjHelper.TypeOfMember(mi));
                string fkey = ForeignKey(o, mi).Name;

                query += string.Format(" inner join [{0}] on [{1}].[{2}] = [{0}].[{2}] ",
                    new object[] { fTable, pTable, fkey });
            }
            return query;
        }

        /// <summary>
        /// Returns the join part of query to retrieve the complex object
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static string FromObjectQuery(Type t)
        {
            return FromObjectQuery(ObjHelper.NewObj(t));
        }
        public static string FromObjectQuery(object o)
        {
            //Assemble inner joins
            string pTable = TableName(o);
            string query = TableName(o);
            foreach (MemberInfo mi in ComplexFields(o))
            {
                string fTable = TableName(ObjHelper.TypeOfMember(mi));
                string fkey = ForeignKey(o, mi).Name;

                query += string.Format(" inner join [{0}] on [{1}].[{2}] = [{0}].[{2}] ",
                    new object[] { fTable, pTable, fkey });
            }
            return query;
        }

        public static object ReaderToComplexObject(object o, SqlDataReader reader)
        {
            //Nothing back nothing to do
            ValidateReader(reader);

            foreach (MemberInfo mi in ComplexFields(o))
            {
                //Get the complexObject from the base
                object co = Convert(reader, ObjHelper.GetMemberValue(o,mi));

                //Set the complexObj now that it has been created/converted
                ObjHelper.SetMember(o,co, mi);
            }

            //Set up base obj
            o = Convert(reader, o);

            return o;
        }

        public static object RetrieveComplexObject(object o, object pk, SqlConnection c)
        {
            //Construct query
            string query = RetrieveComplexQuery(o);

            //Add where clause
            query += string.Format(" where [{0}] = @pkey ", PrimaryKeyField(o).Name);

            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                //Setup command
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@pkey", pk);

                SqlDataReader reader = cmd.ExecuteReader();

                o = ReaderToComplexObject(o, reader);

                //close reader
                reader.Close();
                return o;
            }
        }

        /// <summary>
        /// Retrieves a complex object by BaseObjectId
        /// </summary>
        /// <param name="o"></param>
        /// <param name="boid"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public static object RetrieveByBaseObjectId(object o, object boid, SqlConnection c)
        {
            string query = string.Format
                ("{0} where [{1}].[BaseObjectId] = @BaseObjectId",
            RetrieveComplexQuery(o),
            Helper.SqlHelper.TableName(o));

            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                cmd.Parameters.AddWithValue("@BaseObjectId", boid);
                SqlDataReader reader = cmd.ExecuteReader();
                //if (reader.Read())
                //    o = Helper.SqlHelper.Convert(reader, o);
                o = ReaderToComplexObject(o, reader);
                reader.Close();
            }
            return o;
        }
    }
}